Computus – Calculating the Date of Easter

This may seem like an odd topic, but it is nevertheless quite interesting. Quite aside from any ecclesiastical purposes, it is useful to know when Easter is going to be, as in many countries there are public holidays associated with it. As well as telling you when you might be able to get 16 days off by only using 8 days of leave (or alternatively, telling you when all the places you want to go will be super busy), Easter has significant financial implications too. Supermarkets usually have much higher sales volumes, and of course, banks and the stock market are closed for four days in a row. The calculation of the date of Easter is of such historical importance that it even has its own name – “Computus”.

Lots of businesses use Microsoft Excel for all manner of purposes – often financial. Therefore it would probably be useful to be able to calculate the date of Easter in an Excel formula. Hahaha. Let’s get started shall we?

I. Not Astronomical

Easter – the first Sunday after the first Full Moon on or after the Vernal Equinox, right…?

Well, almost.  Years ago, when I first attempted to calculate the date on which Easter falls, I tried using this definition.  It does work for quite a lot of years, but it is off by 7, 21, 28 or 35 days in either direction often enough to be entirely useless.  Here was my first attempt:

• Using year 2000 as a baseline
• Baseline Vernal Equinox (BVE) – 20/03/2000 07:35 (Excel date 36605.316)
• Baseline Full Moon (BFM) – 20/03/2000 05:44 (Excel date 36605.239)
• Lunar Month Length (LML) – 29.530588861 days
• This Full Moon is before the equinox, so from this, we can calculate that the next Full Moon is 18/04/2000 18:28.  This is a Tuesday, so the first Sunday on or after this date is the 23/04/2000
• This is the correct date of Easter in 2000, and from here it is quite straight-forward to:
• Calculate the next equinox (NE) – add 365.2425 days to the previous equinox for the length of the tropical year, subtract 1 if the year is divisible by 4 for leap years, then add one back if divisible by 100, and subtract one again if divisible by 400 for the Gregorian calendar leap year adjustments.
• Calculate the next Full Moon after this (NFM) – in Excel, this is BFM+LML*CEILING((NE-BFM)/LML,1)
• Calculate the date of Easter Sunday – in Excel, this is NFM+8-WEEKDAY(NFM,1)

We can keep doing this, to calculate the date on which Easter should fall, and it works very well for several years.

Then fails suddenly in 2019, suggesting a date 28 days before the actual date of Easter 2019

It goes back to working after this, only to fail again in 2025, then 2038, and so on.

If you think that this seems really close, and it must be able to be fixed, don’t.  That way madness lies.  Fundamentally, the date that the church calculates Easter to be is not based upon the astronomical Full Moon, or the astronomical Vernal Equinox.  The key word missing in the definition of Easter above is “ecclesiastical”.  Easter is the first Sunday after the first ecclesiastical Full Moon on or after the ecclesiastical Vernal Equinox.

II. Making things Needlessly Complicated

Whenever you look up how to calculate the date of Easter, you inevitably either get bogged down in tables full of roman numerals, paragraphs about the Epact, the Saltus, embolismic months and Paschal New Moons, or are shown a neat and tidy formula that makes absolutely no sense, and is very difficult to reconcile with the tables and paragraphs you just ploughed through.

Here is a selection of some Excel formulas from various places on the internet that are purported to calculate Easter:

Accurate until 2078

=FLOOR("5/"&DAY(MINUTE(year/38)/2+56)&"/"&year,7)-34

=FLOOR(DAY(MINUTE(year/38)/2+56)&"/5/"&year,7)-34

Accurate until 2203

=ROUND(DATE(year,4,1)/7+MOD(19*MOD(year,19)-7,30)*14%,0)*7-6

=FLOOR(DATE(year,3,MOD(18.37*MOD(year,19)-6,29)),7)+29

=DATE(year,3,29.56+0.979*MOD(204-11*MOD(year,19),30) - WEEKDAY(DATE(year,3,28.56+0.979*MOD(204-11*MOD(year,19),30))))

=DOLLAR(("4/"&year)/7+MOD(19*MOD(year,19)-7,30)*14%,)*7-6

Notably however, none of these stay accurate for more than a couple of hundred years, and none of them really make much sense.  There are algorithms that have been written which are entirely accurate, but which cannot reasonably be shoe-horned into an Excel formula (the usual issue being that they would be too long or have too many nested brackets), and even these can be troublesome to explain.

III. The Answer

Here, I will put forward a (reasonably) concise Excel formula, which will completely accurately calculate the date of Easter, from 1900 to arbitrarily far into the future (unfortunately, Excel’s dates have anomalies before 1900, making going earlier than this a whole new problem).  I will also explain it, and how each element of it contributes to calculating Easter accurately.

So – without further ado – an Excel formula to accurately calculate the date of Easter Sunday:

=DATE(year,3,21)+
INDEX(CHOOSE({1;2},
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,28},
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,27,28}),
IF(MOD(year,19)>=11,2,1),
MOD(15+INT(year/100)-INT(INT(year/100)/4)-INT((13+8*INT(year/100))/25)-11*MOD(year,19),30)
+1)+8-WEEKDAY(
DATE(year,3,21)+
INDEX(CHOOSE({1;2},
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,28},
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,27,28}),
IF(MOD(year,19)>=11,2,1),
MOD(15+INT(year/100)-INT(INT(year/100)/4)-INT((13+8*INT(year/100))/25)-11*MOD(year,19),30)
+1),1)

You may notice that this takes the form of [formula]+8-WEEKDAY([formula],1).  This is the same as the final step in the misguided “astronomical Easter” approach, finding the first Sunday after the date given by [formula] (a date which should be the first ecclesiastical Full Moon on or after the ecclesiastical Vernal Equinox), so we can ignore this, and focus on what is going on within [formula]:

DATE(year,3,21)+
INDEX(CHOOSE({1;2},
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,28},
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,27,28}),
IF(MOD(year,19)>=11,2,1),
MOD(15+INT(year/100)-INT(INT(year/100)/4)-INT((13+8*INT(year/100))/25)-11*MOD(year,19),30)
+1)

IV. The Explanation

In fact, for the vast majority of the time, this formula for the ecclesiastical Vernal Equinox gives the same results as the one below, so first let’s explain this slightly simpler formula, before we get bogged down in all that INDEX(CHOOSE(…)…+1) nonsense:

DATE(year,3,21)+
MOD(15+INT(year/100)-INT(INT(year/100)/4)-INT((13+8*INT(year/100))/25)-11*MOD(year,19),30)

The first thing to note is that the “ecclesiastical Vernal Equinox” is defined by the church to be the 21^st March, hence the DATE(year,3,21).  No more worrying about having to calculate the date of the equinox, because as far as the church is concerned, it is always the same date.  The rest of the formula then just calculates how many days after this the ecclesiastical Full Moon falls.

The second thing to note is that the ecclesiastical lunar month is defined to start on the ecclesiastical New Moon, and the ecclesiastical Full Moon is defined to be the 14^th day of the lunar month (or 13 days after the day of the New Moon), regardless of the length of the lunar month in question (which can be 28 to 31 days long, although here we will treat all months as 29 or 30 days long, as the complexities of the lunar calendar giving rise to these extra-long and short months don’t have any effect on the Easter calculation).  As we are interested in the Full Moon, and it is defined to be a fixed number of days after the New Moon, rather than in the middle of the month, this allows us another simplification – we don’t need to worry about the New Moon at all.  If the date of the New Moon moves a number of days in one direction relative to the solar calendar, the Full Moon moves exactly the same number of days in the same direction.

Often found in descriptions of the Computus is the “Epact”, which is defined as ‘the age of the moon on 1^st January’.  The age of the moon refers to how many days it is since the New Moon, so this is the number of days since the last New Moon at 1^st January.  What we need, is a related quantity – the number of days since the Vernal Equinox, at the next Full Moon.  It is possible (and indeed usual) to calculate Easter using the Epact, but it adds needless complexity, due to the subtleties of the ecclesiastical lunar calendar.  The particular Full Moon we are interested in (the one after the equinox) is known as the Paschal Full Moon, and the part of the formula MOD(15+…,30) is therefore a calculation of how the Paschal Full Moon moves relative to the equinox.

A normal ecclesiastical lunar year contains 6 lunar months with 29 days, and 6 with 30 days for a total of 354 days.  This is 11 days less than a normal solar year with 365 days, which means that every year, the date of the Paschal Full Moon moves closer to the equinox by 11 days.  Obviously at some point it will move to before the equinox, at which point to prevent this an additional “embolismic month” of 30 days is added giving that year 13 lunar months, and pushing the Paschal Full Moon 30 days in the other direction.  11 x 19 = 209, while 30 x 7 = 210 showing us that over a 19-year cycle (called the Metonic Cycle) we expect to add embolismic months 7 times.  The difference of 1 day over this cycle is already incorporated into the ecclesiastical lunar calendar – every 19^th lunar year is 1 day shorter than usual (this is called the “Saltus”), bringing us back into sync.

This explains the MOD([stuff]-11*MOD(year,19),30) part of the formula – the year modulo 19 gives us where we are in the Metonic Cycle, which multiplied by -11, gives us how many blocks of 11 days the Paschal Full Moon has moved back relative to the equinox.  Once we roll past the next multiple of 19, we are back to where we started, thanks to the Saltus.  Taking this modulo 30, then ensures that we are adding the embolismic months at the appropriate times – as soon as the number goes negative, we add another 30-day month in, pushing the Paschal Full Moon back after the equinox.

At this juncture, it is worth pointing out that the ecclesiastical lunar calendar was based on the Julian calendar, which had a leap year every 4 years.  This means that this is already baked into the movement of the Paschal Full Moon – in a normal leap year, the ecclesiastical lunar calendar adds an extra day to one of the lunar months, just like the solar calendar, so there is no additional movement of the Paschal Full Moon to worry about.  That said, the move to the Gregorian calendar now means that every 100 years (except every 400^th year) there is a movement of one day in the opposite direction, as the ecclesiastical lunar calendar adds an extra day, while the solar calendar does not.

This is where the [stuff] comes in – this consists of adjustments to the movement of the Paschal Full Moon:

15+INT(year/100)-INT(INT(year/100)/4)-INT((13+8*INT(year/100))/25)

The +INT(year/100)-INT(INT(year/100)/4) part is quite straight-forward – as mentioned, the ecclesiastical lunar calendar has leap years every 4 years, so normal leap years don’t affect the movement of the Paschal Full Moon.  According to the Gregorian calendar however, every 100^th year is not a leap year, so the Paschal Full Moon will move one day further from the equinox, as a day has been added to the lunar calendar but not the solar calendar.  Further to this, every 400^th year is made into a leap year again, meaning that we must undo this adjustment.  The INT(year/100) rounds year/100 down to the nearest integer, giving us a number that stays the same from year to year, until it ticks up by one every century, for example in 2010, this will be 20.  When wrapped in the MOD(…,30) this causes an increment of 1 day every 100 years.  The 400 year adjustment works similarly.  This adjustment was not necessary in the days when the Julian calendar was being used, but because the ecclesiastical lunar calendar was not updated to have leap years in the same places when the solar calendar was switched to the Gregorian calendar, these additional adjustments were required.

We then move to the rather more unexpected -INT((13+8*INT(year/100))/25) adjustment.  Over the course of the 19-year Metonic Cycle, we have a total of 19 x 12 + 7 = 235 lunar months.  This would be made up of 19 x 6 = 114 months of 29 days, and 19 x 6 + 7 = 121 months of 30 days, except for the fact that the Saltus reduces one of these months by a day, and that within this period there will be on average 4.75 leap years, increasing 4.75 of the months by 1 day over the course of the cycle.  In total, this gives us 114 x 29 + 121 x 30 – 1 + 4.75 = 6939.75 days per cycle on average.  Compare this to the actual length of the astronomical lunar month: 29.530588861 days times 235 lunar months gives us 6939.6884 days per cycle.  This discrepancy of 0.0616 days every 19 years may seem small, but over the course of 2500 years, it adds up to 8 days.  When the Gregorian calendar was introduced, it was decided that they would adjust the Computus to compensate for this discrepancy. Now we can see where the numbers in the formula come from – in a similar way to the leap years, this will move the Paschal Full Moon one day closer to the equinox 8 times every 2500 years.  The way this is set up means that rather than subtracting them evenly, every 312 years, it subtracts one every 300 years, 7 times in a row, then subtracts the final one after 400 years before starting again.  The 13 is a fairly arbitrary constant, added to make this sequence start at the right point.

Similarly, the 15 in this formula is another arbitrary constant (technically corresponding to the position of the Paschal Full Moon when the Julian Epact is 0), meaning that if we plug the year 2010 into the formula, we get 15 + 20 – 5 – 6 = 24.  MOD(24-11*MOD(2010,19),30) then gives us 9, telling us that the Paschal Full Moon is 9 days after the equinox (21^st March), which is 30^th March.

This fully explains the formula that we set out to understand at the beginning of this section:

DATE(year,3,21)+
MOD(15+INT(year/100)-INT(INT(year/100)/4)-INT((13+8*INT(year/100))/25)-11*MOD(year,19),30)

The equinox, adjusted for the position in the Metonic Cycle, adjusted for Gregorian leap years and finally adjusted for the discrepancy with the astronomical lunar month.

V. Almost there

Unfortunately, there is a further wrinkle, as this will fail to give the correct answer occasionally, once again due to the shift to the Gregorian calendar.  In the original computus, created for the Julian calendar, there were none of these adjustments – it was just based on the Metonic Cycle, which means that the dates of the Paschal Full Moon repeated every 19 years, allowing them to be calculated with DATE(year,3,21)+MOD(15-11*MOD(year,19),30) instead.  The thing with this, is that because it is a repeating 19 year cycle, it does not take every value modulo 30.  In fact, it goes 15, 4, 23, 12, 1, 20, 9, 28, 17, 6, 25, 14, 3, 22, 11, 0, 19, 8, 27 then back to the start.  Critically, 29 does not feature, meaning that the Paschal Full Moon could fall on any one of 19 particular dates from 21st March to 18th April, but not 19th April (which is 29 days after 21st March).  This means that Easter Sunday could range from 22nd March to 25th April.  When we add in the adjustments for the modern Gregorian computus however, this results in the numbers taken by this cycle changing every 100 years or so, meaning that in some centuries a 29 will appear.  This would result in the Paschal Full Moon occasionally falling on 19th April, and Easter Sunday being able to fall on 26th April.

To retain consistency with the range of dates permitted under the Julian calendar, the Catholic Church decided to adjust the date of the Paschal Full Moon to prevent this from occurring – if a 29 ever occurs, it is simply replaced with a 28.  It is never quite that simple though – in any given century, the 19 year cycle will contain either 27 & 28, 27 & 29 or 28 & 29 (never all 3), so in centuries where there is both a 28 and a 29, this would give two years with a 28.  Therefore, in these cycles the 28 is changed to a 27 as well (in centuries without a 29, the 28 is left unchanged).  Capping the movement of the Paschal Full Moon at 28 days would be relatively simple but changing the 28 to a 27 only when there is a 29 in the same Metonic Cycle is more complicated.  If you calculate every one of the 30 possible cycles, It so happens that cycles containing a 28 in the 1^st – 10^th year of the cycle do not contain a 29, whereas if the 28 occurs in the 11^th – 19^th year, the cycle will contain a 29 as well.  This means that we can check where we are in each cycle, and if a 28 occurs in the 11^th year or later, we can reduce it to 27, whilst always reducing a 29 to a 28.

This is all very well to describe, but how do we create an Excel formula to calculate it?  The method I have used is to create a matrix with 30 columns (one for each possible number modulo 30), and 2 rows (for cycle years 1-10 and years 11-19).  To create this matrix, I have used the CHOOSE function, but for the “index_num” argument, rather than putting a single number, I have put an array across multiple rows {a;b}.  When the CHOOSE is wrapped in an INDEX, this allows it to create an array with the a^th argument of CHOOSE in the 1^st row, the b^th argument of CHOOSE in the 2^nd row, etc.  For these arguments, I have put an array of the numbers I want the Paschal Full Moon to take.

DATE(year,3,21)+
INDEX(CHOOSE({1;2},
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,28},
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,27,28}),
IF(MOD(year,19)>=11,2,1),
MOD(15+INT(year/100)-INT(INT(year/100)/4)-INT((13+8*INT(year/100))/25)-11*MOD(year,19),30)
+1)

To make it a bit easier to see what is going on here, and show clearly which bits of the formula are yet to be explained, let’s replace the majority of the formula from the previous section with something shorter. Below, MOD(15+INT(year/100)-INT(INT(year/100)/4)-INT((13+8*INT(year/100))/25)-11*MOD(year,19),30) has been shortened to [calc], showing the remaining components of the formula:

DATE(year,3,21)+INDEX(CHOOSE({1;2},
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,28},
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,27,28}),
IF(MOD(year,19)>=11,2,1),[calc]+1)

The INDEX is then picking the first row if we are in years 1-10 of the Metonic Cycle, and the second if we are in years 11-19.  Within this row, it is then taking the [calc]+1 column.  Here we can see that [calc] will take a value from 0 to 29, so we add 1 to allow us to select column 1 to 30.  Most columns then contain a number 1 less than the column number, bringing us back to the number [calc] gave us, but columns 29 and 30 are slightly different, containing the adjusted values discussed above.

Finally, we have explained the full formula:

=DATE(year,3,21)+
INDEX(CHOOSE({1;2},
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,28},
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,27,28}),
IF(MOD(year,19)>=11,2,1),
MOD(15+INT(year/100)-INT(INT(year/100)/4)-INT((13+8*INT(year/100))/25)-11*MOD(year,19),30)
+1)+8-WEEKDAY(
DATE(year,3,21)+
INDEX(CHOOSE({1;2},
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,28},
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,27,28}),
IF(MOD(year,19)>=11,2,1),
MOD(15+INT(year/100)-INT(INT(year/100)/4)-INT((13+8*INT(year/100))/25)-11*MOD(year,19),30)
+1),1)

It is much more complicated than the equivalent Julian calendar formula:

DATE(year,3,21)+MOD(15-11*MOD(year,19),30)
+8-WEEKDAY(DATE(year,3,21)+MOD(15-11*MOD(year,19),30),1)

And more complicated than the hypothetical “astronomical” formula for the Full Moon after the Vernal Equinox:

BFM+LML*CEILING((BVE-BFM+365.2425*(year-2000)
-INT((year-2000)/4)+INT((year-2000)/100)-INT((year-2000)/400))/LML,1)

But although it is significantly longer than the other formulas that can be found online to calculate Easter, I don’t think it is more complicated.  In fact, I think it is far simpler – each component is justifiable and makes sense on its own.

Appendix I

Further to this, we can replace the WEEKDAY function if we want, using how we know leap years affect the days of the week.  Taking the full formula above with the structure:

DATE(year,3,21)+INDEX(…)+8-WEEKDAY(DATE+INDEX(…))

And changing it to this:

DATE(year,3,21)+INDEX(…)+7-MOD(
2+year+INT(year/4)-INT(year/100)+INT(INT(year/100)/4)+INDEX(…),7)

We get a revised formula of:

=DATE(year,3,21)+
INDEX(CHOOSE({1;2},
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,28},
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,27,28}),
IF(MOD(year,19)>=11,2,1),
MOD(15+INT(year/100)-INT(INT(year/100)/4)-INT((13+8*INT(year/100))/25)-11*MOD(year,19),30)
+1)+7-MOD(
2+year+INT(year/4)-INT(year/100)+INT(INT(year/100)/4)+
INDEX(CHOOSE({1;2},
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,28},
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,27,28}),
IF(MOD(year,19)>=11,2,1),
MOD(15+INT(year/100)-INT(INT(year/100)/4)-INT((13+8*INT(year/100))/25)-11*MOD(year,19),30)
+1),7)

Appendix II

Using this knowledge, we can also dig into why some of the other simpler formulas for Easter fail when they do.  Going from 2099 to 2100, there is a 100-year adjustment of +1 day, but the 2500-year lunar adjustment of -1 day happens at the same time, cancelling it out.  At 2200, the 100-year adjustment happens again, but nothing offsets it.  Similarly going from 1999 to 2000, the 100-year adjustment of +1 day is offset by the 400-year adjustment of -1 day.  This means that a formula can ignore the 100-, 400- and 2500-year adjustment cycles, and still accurately calculate Easter between 1900 and 2199.  We calculated above that the adjustment we labelled [stuff] was 24 in the year 2010, which means it should be 24 from 1900 to 2199.  With an understanding of modular arithmetic, we can observe that 19 = -11 mod 30, and 24 = -6 mod 30, allowing us to deduce that MOD(24-11*MOD(year,19),30) is always equal to MOD(19*MOD(year,19)-6,30).  This is starting to look suspiciously similar to a component in several of the formulas from the internet listed in section II above.  In fact, if we use this observation, and allow ourselves to simplify the full formula to only work from 1900 to 2199 we can get to this formula:

=DATE(year,3,21)+
INDEX({0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,27,28},
1,
MOD(19*MOD(year,19)-6,30)
+1)+8-WEEKDAY(
DATE(year,3,21)+
INDEX({0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,27,28},
1,
MOD(19*MOD(year,19)-6,30)
+1),1)

Which we can simplify even further to this:

=DATE(year,3,21)+
MOD(19*MOD(year,19)-6,30)-INT(MOD(19*MOD(year,19)-6,30)/28)
+8-WEEKDAY(
DATE(year,3,21)+
MOD(19*MOD(year,19)-6,30)-INT(MOD(19*MOD(year,19)-6,30)/28)
,1)